package com.wangshuqi.excel;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;

public class ExcelMain {

    public static void main(String[] args) throws IOException {
        InputStream stream = new FileInputStream(new File("D:\\work_project\\java-excel\\java-excel\\src\\main\\resources\\汇率导入模板.xlsx"));

        Workbook workbook = new XSSFWorkbook(stream);
        // 获取第一个sheet页
        Sheet sheet = workbook.getSheetAt(0);
        // 获取第一行数据
        int firstRowNum = sheet.getFirstRowNum();
        Row firstRow = sheet.getRow(firstRowNum);
        if (null == firstRow) {
            System.out.println("解析Excel失败，在第一行没有读取到任何数据！");
        }
        // 解析每一行的数据，构造数据对象
        int rowStart = firstRowNum + 1;
        int rowEnd = sheet.getPhysicalNumberOfRows();
        for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {
            Row row = sheet.getRow(rowNum);
            if (null == row) {
                continue;
            }
            int cellTotal = row.getPhysicalNumberOfCells();
            for (int i = 0; i < cellTotal; i++) {
                Cell cell = row.getCell(i);
                String value = convertCellValueToString(cell);
                System.out.print(value + "\t");
            }
            System.out.println();
        }
        stream.close();
    }

    private static String convertCellValueToString(Cell cell) {
        if (cell == null) {
            return null;
        }
        String returnValue = null;
        switch (cell.getCellType()) {
            case NUMERIC:   //数字
                Double doubleValue = cell.getNumericCellValue();
                /*returnValue = String.valueOf(doubleValue);*/
                // 格式化科学计数法，取一位整数
                /*DecimalFormat df = new DecimalFormat("0");
                returnValue = df.format(doubleValue);*/
                DecimalFormat decimalFormat = new DecimalFormat("#.############");
                returnValue = decimalFormat.format(doubleValue);
                break;
            case STRING:    //字符串
                returnValue = cell.getStringCellValue();
                break;
            case BOOLEAN:   //布尔
                Boolean booleanValue = cell.getBooleanCellValue();
                returnValue = booleanValue.toString();
                break;
            case BLANK:     // 空值
                break;
            case FORMULA:   // 公式
                returnValue = cell.getCellFormula();
                break;
            case ERROR:     // 故障
                break;
            default:
                break;
        }
        return returnValue;
    }
}
